package personal.study.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.sql.Date;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import personal.study.form.MaterialForm;

public class DBMaterial {
	private static Log log = LogFactory.getLog(DBMaterial.class);

	public MaterialForm findByPriaryKey(Connection con, String MaterialCode)
			throws Exception {
		MaterialForm materialForm = null;
		StringBuffer buffer = new StringBuffer(100);
		buffer.append("SELECT ");
		buffer.append("MaterialCode,");
		buffer.append("SupplierCode,");
		buffer.append("MaterialName,");
		buffer.append("MaterialPrice,");
		buffer.append("MaterialAmount,");
		buffer.append("SupplyDate,");
		buffer.append("Flag,");
		buffer.append("Remark ");
		buffer.append("FROM Materials WHERE MaterialCode='");
		buffer.append(MaterialCode);
		buffer.append("'");

		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = con.prepareStatement(buffer.toString());
			rs = ps.executeQuery();
			if (rs.next()) {
				materialForm = new MaterialForm();
				materialForm.setMaterialCode(rs.getString("MaterialCode"));
				materialForm.setSupplierCode(rs.getString("SupplierCode"));
				materialForm.setMaterialName(rs.getString("MaterialName"));
				materialForm.setMaterialPrice(rs.getDouble("MaterialPrice"));
				materialForm.setMaterialAmount(rs.getInt("MaterialAmount"));
				materialForm.setSupplyDate(rs.getDate("SupplyDate"));
				materialForm.setFlag(rs.getString("Flag"));
				materialForm.setRemark(rs.getString("Remark"));
			}
		} catch (Exception e) {
			log.error(e.getMessage());
		} finally {
			ps.close();
			rs.close();
		}

		return materialForm;
	}

	public Collection<MaterialForm> queryMaterial(Connection con,
			String conditions, int currentPage, int rowsPerPage)
			throws Exception {
		Collection<MaterialForm> col = new ArrayList<MaterialForm>();
		StringBuffer buffer = new StringBuffer(200);
		buffer.append("SELECT ");
		buffer.append("materialCode,");
		buffer.append("supplierCode,");
		buffer.append("materialName,");
		buffer.append("materialPrice,");
		buffer.append("materialAmount,");
		buffer.append("supplyDate,");
		buffer.append("flag,");
		buffer.append("remark ");
		buffer.append("FROM Materials WHERE ");
		buffer.append(conditions);
		buffer.append(" LIMIT ");
		buffer.append((currentPage - 1) * rowsPerPage);
		buffer.append(",");
		buffer.append(rowsPerPage);

		log.info(buffer.toString());

		PreparedStatement ps = null;
		ResultSet rs = null;
		MaterialForm materialForm = null;
		try {
			ps = con.prepareStatement(buffer.toString());
			rs = ps.executeQuery();
			while (rs.next()) {
				materialForm = new MaterialForm();
				materialForm.setMaterialCode(rs.getString("materialCode"));
				materialForm.setSupplierCode(rs.getString("supplierCode"));
				materialForm.setMaterialName(rs.getString("materialName"));
				materialForm.setMaterialPrice(rs.getDouble("materialPrice"));
				materialForm.setMaterialAmount(rs.getInt("materialAmount"));
				materialForm.setSupplyDate(rs.getDate("supplyDate"));
				materialForm.setFlag(rs.getString("flag"));
				materialForm.setRemark(rs.getString("remark"));
				col.add(materialForm);
			}
		} catch (SQLException e) {
			log.error(e.getMessage());
		} finally {
			ps.close();
			rs.close();
		}

		return col;
	}
	
	public Collection<MaterialForm> findByConditions(Connection con, String conditions)
			throws Exception {
		Collection<MaterialForm> col = new ArrayList<MaterialForm>();
		StringBuffer buffer = new StringBuffer(200);
		buffer.append("SELECT ");
		buffer.append("materialCode,");
		buffer.append("supplierCode,");
		buffer.append("materialName,");
		buffer.append("materialPrice,");
		buffer.append("materialAmount,");
		buffer.append("supplyDate,");
		buffer.append("flag,");
		buffer.append("remark ");
		buffer.append("FROM Materials WHERE ");
		buffer.append(conditions);

		log.info(buffer.toString());

		PreparedStatement ps = null;
		ResultSet rs = null;
		MaterialForm materialForm = null;
		try {
			ps = con.prepareStatement(buffer.toString());
			rs = ps.executeQuery();
			while (rs.next()) {
				materialForm = new MaterialForm();
				materialForm.setMaterialCode(rs.getString("materialCode"));
				materialForm.setSupplierCode(rs.getString("supplierCode"));
				materialForm.setMaterialName(rs.getString("materialName"));
				materialForm.setMaterialPrice(rs.getDouble("materialPrice"));
				materialForm.setMaterialAmount(rs.getInt("materialAmount"));
				materialForm.setSupplyDate(rs.getDate("supplyDate"));
				materialForm.setFlag(rs.getString("flag"));
				materialForm.setRemark(rs.getString("remark"));
				col.add(materialForm);
			}
		} catch (SQLException e) {
			log.error(e.getMessage());
		} finally {
			ps.close();
			rs.close();
		}

		return col;
	}

	public int getCount(Connection con, String conditions) throws Exception {
		int count = 0;
		StringBuffer buffer = new StringBuffer(100);
		buffer.append("SELECT COUNT(*) FROM Materials WHERE ");
		buffer.append(conditions);

		log.info(buffer.toString());

		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = con.prepareStatement(buffer.toString());
			rs = ps.executeQuery();
			rs.next();
			count = rs.getInt(1);
		} catch (Exception e) {
			log.error(e.getMessage());
		} finally {
			ps.close();
			rs.close();
		}
		return count;
	}

	public boolean insert(Connection con, MaterialForm materialForm)
			throws Exception {
		boolean result = false;

		StringBuffer buffer = new StringBuffer(100);
		buffer.append("INSERT INTO Materials (");
		buffer.append("MaterialCode,");
		buffer.append("SupplierCode,");
		buffer.append("MaterialName,");
		buffer.append("MaterialPrice,");
		buffer.append("MaterialAmount,");
		buffer.append("SupplyDate,");
		buffer.append("Flag,");
		buffer.append("Remark) ");

		if (log.isDebugEnabled()) {
			StringBuffer debugBuffer = new StringBuffer(buffer.length() * 4);
			debugBuffer.append(buffer.toString());
			debugBuffer.append("VALUES (");
			debugBuffer.append("'").append(materialForm.getMaterialCode())
					.append("',");
			debugBuffer.append("'").append(materialForm.getSupplierCode())
					.append("',");
			debugBuffer.append("'").append(materialForm.getMaterialName())
					.append("',");
			debugBuffer.append("'").append(materialForm.getMaterialPrice())
					.append("',");
			debugBuffer.append("'").append(materialForm.getMaterialAmount())
					.append("',");
			debugBuffer.append("'").append(materialForm.getSupplyDate())
					.append("',");
			debugBuffer.append("'").append(materialForm.getFlag()).append("',");
			debugBuffer.append("'").append(materialForm.getRemark())
					.append("'");
			log.debug(debugBuffer.toString());
		}

		buffer.append("VALUES (?,?,?,?,?,?,?,?)");

		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(buffer.toString());
			ps.setString(1, materialForm.getMaterialCode());
			ps.setString(2, materialForm.getSupplierCode());
			ps.setString(3, materialForm.getMaterialName());
			ps.setDouble(4, materialForm.getMaterialPrice());
			ps.setInt(5, materialForm.getMaterialAmount());
			ps.setDate(6, Date.valueOf(materialForm.getSupplyDate().toString()));
			ps.setString(7, materialForm.getFlag());
			ps.setString(8, materialForm.getRemark());
			ps.executeUpdate();
			result = true;
		} catch (Exception e) {
			log.error(e.getMessage());
			result = false;
		} finally {
			ps.close();
		}

		return result;
	}

	public boolean update(Connection con, MaterialForm materialform)
			throws Exception {
		boolean result = false;
		StringBuffer buffer = new StringBuffer(100);
		buffer.append("UPDATE Materials SET  ");
		buffer.append("SupplierCode=?,");
		buffer.append("MaterialName=?,");
		buffer.append("MaterialPrice=?,");
		buffer.append("MaterialAmount=?,");
		buffer.append("SupplyDate=?, ");
		buffer.append("Flag=?,");
		buffer.append("Remark=? ");

		buffer.append("WHERE ");
		buffer.append("MaterialCode=?");

		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(buffer.toString());
			ps.setString(1, materialform.getSupplierCode());
			ps.setString(2, materialform.getMaterialName());
			ps.setDouble(3, materialform.getMaterialPrice());
			ps.setInt(4, materialform.getMaterialAmount());
			ps.setDate(5, Date.valueOf(materialform.getSupplyDate().toString()));
			ps.setString(6, materialform.getFlag());
			ps.setString(7, materialform.getRemark());
			ps.setString(8, materialform.getMaterialCode());
			ps.executeUpdate();
			result = true;
		} catch (Exception e) {
			log.error(e.getMessage());
			result = false;
		} finally {
			ps.close();
		}
		return result;
	}
	
	public void delete(Connection con, String MaterialCode) throws Exception {
		StringBuffer buffer = new StringBuffer(100);
		buffer.append("DELETE FROM Materials WHERE ");
		
		if(log.isDebugEnabled()){
			StringBuffer debugBuffer = new StringBuffer(buffer.toString());
			debugBuffer.append("MaterialCode='");
			debugBuffer.append(MaterialCode);
			debugBuffer.append("'");
			log.debug(debugBuffer.toString());
		}

		buffer.append("MaterialCode=?");

		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(buffer.toString());
			ps.setString(1, MaterialCode);
			ps.executeUpdate();
		} catch (Exception e) {
			log.error(e.getMessage());
		} finally {
			ps.close();
		}

	}
}
